package com.onlineMIS.ORM.DAO.headQ.finance;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.hibernate.mapping.Array;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.onlineMIS.ORM.DAO.BaseDAO;
import com.onlineMIS.ORM.entity.base.BaseOrder;
import com.onlineMIS.ORM.entity.headQ.custMgmt.HeadQCust;
import com.onlineMIS.ORM.entity.headQ.finance.ChainFinanceTrace;
import com.onlineMIS.ORM.entity.headQ.finance.ExpenseHq;
import com.onlineMIS.ORM.entity.headQ.finance.FinanceBill;
import com.onlineMIS.ORM.entity.headQ.finance.FinanceCategory;
import com.onlineMIS.ORM.entity.headQ.finance.FinancePrestoreInfoVO;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrder;
import com.onlineMIS.ORM.entity.headQ.report.HeadQDealingsReportVO;
import com.onlineMIS.action.headQ.finance.FinanceActionFormBean;
import com.onlineMIS.common.Common_util;
import com.onlineMIS.common.DateUtils;
import com.onlineMIS.filter.SystemParm;

@Repository
public class ChainFinanceTraceImpl extends BaseDAO<ChainFinanceTrace> {

	@Autowired
	private FinanceBillImpl financeBillImpl;

	/**
	 * 获取客户预存金金额
	 * @param categoryId
	 * @param custId
	 * @exception 注意：这里的客户预存金 是在预存金清账之后的客户预存金合计
	 * @return
	 * @throws ParseException 
	 */
	public double getSumOfFinanceCategory(int categoryId, int custId) throws ParseException{
		Object[] values = new Object[]{custId, categoryId};
		
		String hql = "SELECT SUM(amount) from ChainFinanceTrace WHERE custId =? AND categoryId=? and date >= '"  + SystemParm.getPrepayFlowDateS() +"'";
		
		List<Object> results = this.executeHQLSelect(hql, values,null, true);
		
		if (results != null && results.size() > 0){
			double sum = Common_util.getDouble(results.get(0));
			return sum;
		} else 
			return 0;
	}
	
	public Map<Integer, Double> getSumOfFinanceCategory(List<Integer> ids) throws ParseException{
		Map<Integer, Double> map = new HashMap<Integer, Double>();
		
		String hql = "SELECT custId, SUM(amount) from ChainFinanceTrace WHERE custId in "+Common_util.parseList2SQLString(ids)+" AND categoryId= "+FinanceCategory.PREPAY_ACCT_TYPE+" and date >= '"  + SystemParm.getPrepayFlowDateS() +"' group by custId";
		
		List<Object> results = this.executeHQLSelect(hql, null,null, true);
		
		if (results.size() != 0) {
			for (Object record : results) {		
				Object[] ob = (Object[]) record;
				int custId = Common_util.getInt(ob[0]);
				double accmt = Common_util.getDouble(ob[1]);
				map.put(custId, accmt);
			}
		}
        return map;
	}
	
	
	/**
	 * 这里将chain_id  修改为cust_id  
	 * @param categoryId
	 * @param chainId
	 * @return
	 */
	public double getSumOfFinanceCategoryTwo(int categoryId, int custId){
		Object[] values = new Object[]{custId, categoryId};
		
		String hql = "SELECT SUM(amount) from ChainFinanceTrace WHERE custId =? AND categoryId=?";
		
		List<Object> results = this.executeHQLSelect(hql, values,null, true);
		
		if (results != null && results.size() > 0){
			double sum = Common_util.getDouble(results.get(0));
			return sum;
		} else 
			return 0;
	}


	public ChainFinanceTrace getByPK(int custId, int chainId, int type, String billId) {
		DetachedCriteria criteria = DetachedCriteria.forClass(ChainFinanceTrace.class);
		criteria.add(Restrictions.eq("custId", custId));
		criteria.add(Restrictions.eq("chainId", chainId));
		criteria.add(Restrictions.eq("categoryId", type));
		criteria.add(Restrictions.eq("billId", billId));
		List<ChainFinanceTrace> traces = getByCritera(criteria, true);
		if (traces == null || traces.size() == 0)
			return null;
		else 
			return traces.get(0);
	}


	/**
	 * 获取预存款账目当前账目金额(预存金合计）
	 * @param categoryId
	 * @return
	 * @throws ParseException 
	 */
	public double getSumAdvancePay(String date,List<Integer> custIds)  { 
		double sumOfPrepay = 0;
		List<Object> resList  = new ArrayList<Object>();
		String dateSql = "";
		if (StringUtils.isNotBlank(date)) {
			dateSql += "and date >= " + date;
		}
		String rechargeHql = "SELECT SUM(amount) from ChainFinanceTrace WHERE categoryId= " + FinanceCategory.PREPAY_ACCT_TYPE + dateSql + "  and  custId in "+Common_util.parseList2SQLString(custIds);		

		List<Object> results = this.executeHQLSelect(rechargeHql, resList.toArray(),null, true);
		
		if (results != null && results.size() > 0){
			sumOfPrepay = Common_util.getDouble(results.get(0));
		}
			
		return sumOfPrepay;		
	}
	

	
	/**
	 * 获取预存款账目流水 - 第一行余额信息
	 * @param categoryId
	 * @return
	 * @throws ParseException 
	 */
	public double getFirstPrepayAmount(String date) throws ParseException { 
		double sumOfPrepay = 0;
		List<Object> resList  = new ArrayList<Object>();
		String rechargeHql = "SELECT SUM(amount) from ChainFinanceTrace WHERE categoryId= " + FinanceCategory.PREPAY_ACCT_TYPE + " and date between '" + SystemParm.getPrepayFlowDateS() +"'and'"  + date +"'";		

		List<Object> results = this.executeHQLSelect(rechargeHql, resList.toArray(),null, true);
		
		if (results != null && results.size() > 0){
			sumOfPrepay = Common_util.getDouble(results.get(0));
		}
			
		return sumOfPrepay;		
	}
	
	public double getFirstPrepayAmount(Date startDate, Date endDate) {
		double sumOfPrepay = 0;
		List<Object> resList  = new ArrayList<Object>();
		String rechargeHql = "SELECT SUM(amount) from ChainFinanceTrace WHERE categoryId= " + FinanceCategory.PREPAY_ACCT_TYPE + " and date between '" + startDate +"'and'"  + endDate +"'";		

		List<Object> results = this.executeHQLSelect(rechargeHql, resList.toArray(),null, true);
		
		if (results != null && results.size() > 0){
			sumOfPrepay = Common_util.getDouble(results.get(0));
		}
			
		return sumOfPrepay;
	}
	
	
	/**
	 * 获取预存款账户在startDate之前的账户余额
	 * @param categoryId
	 * @return
	 */
	public double selectAmount(Date startDate) { 
		double recharge = this.selectRecharge(startDate);
		double spend = this.selectSpend(startDate);				
		return recharge + spend;
	}
	
	
	/**
	 * 查询充值金额
	 * @param startDate
	 * @return
	 */
	public double selectRecharge(Date startDate) {
		Object[] values = new Object[]{startDate};

		//预存款合计充值金额
		String hql = "SELECT SUM(amount) from ChainFinanceTrace WHERE categoryId= 0 and date <= ?";
		
		List<Object> results = this.executeHQLSelect(hql, values,null, true);
		
		if (results != null && results.size() > 0){
			double sum = Common_util.getDouble(results.get(0));
			return sum;
		}else {
			return 0;
		} 
		
	}
	
	
	/**
	 * 查询消费金额
	 * @param startDate
	 * @return
	 */
	public double selectSpend(Date startDate) {
		Object[] values = new Object[]{FinanceCategory.PREPAY_ACCT_TYPE, startDate};

		//预存款合计充值金额
		String hql = "SELECT SUM(amount) from ChainFinanceTrace WHERE amount < 0 and categoryId= ? and date < ?";
		
		List<Object> results = this.executeHQLSelect(hql, values,null, true);
		
		if (results != null && results.size() > 0){
			double sum = Common_util.getDouble(results.get(0));
			return sum;
		}else {
			return 0;
		} 
		
	}
	
	
	/**
	 * 添加上期预存金 - 根据客户分组查询信息
	 * @param custs
	 * @param startDate
	 * @return
	 * @throws ParseException
	 */
	public Map<Integer, Double> getCtpPrestoreMap(List<HeadQCust> custs, Date date) throws ParseException {
		Map<Integer, Double> map = new HashMap<Integer, Double>();
		List<Integer> custIds = new ArrayList<Integer>();
		for (HeadQCust headQCust : custs) {
			custIds.add(headQCust.getId());
		}
		String hql = "SELECT custId, sum(amount) from ChainFinanceTrace WHERE custId in "+Common_util.parseList2SQLString(custIds)+" AND categoryId= "+FinanceCategory.PREPAY_ACCT_TYPE+" and date >= '"  + SystemParm.getPrepayFlowDateS() +"'" + "  and date < ?  group by custId";
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();

		list.add(date);
		List<Object> results = this.executeHQLSelect(hql, list.toArray(),null, true);
		if (results.size() != 0) {
			for (Object record : results) {		
				Object[] ob = (Object[]) record;
				int custId = Common_util.getInt(ob[0]);
				double accmt = Common_util.getDouble(ob[1]);
				map.put(custId, accmt);
			}
		}
        return map;
	}
	
	


	/**
	 * 添加上期预存金
	 * @param custs
	 * @param startDate
	 * @param vos
	 * @return
	 * @throws ParseException
	 */
	public List<HeadQDealingsReportVO> setCtpPrestore(List<HeadQCust> custs, Date date,List<HeadQDealingsReportVO> vos) throws ParseException {
		if (date.before(SystemParm.getPrepayFlowDate())) {
			date = SystemParm.getPrepayFlowDate();
		}
		Map<Integer, HeadQCust> cuMap = new HashMap<Integer, HeadQCust>();		
		for (HeadQCust cust : custs) {
			cuMap.put(cust.getId(), cust);
		}
		
		Map<Integer, Double> map = getCtpPrestoreMap(custs, date);
		for (HeadQDealingsReportVO vo : vos) {
			double  prestoreAmt = 0;
			if (map.get(vo.getClientId()) !=null) {
				prestoreAmt = map.get(vo.getClientId());
			}
			vo.setCtpPrestore(Common_util.roundDouble(cuMap.get(vo.getClientId()).getPrepayInitialAmount() + prestoreAmt, 2));
		}

		return vos;
	}




}
